<?php


class MessagereplyTable extends Doctrine_Table
{
    
    public static function getInstance()
    {
        return Doctrine_Core::getTable('Messagereply');
    }

//    public function getAllReadStatus() {
//        $q = $this->createQuery('m')
//                    ->select("m.messageid, count(1) as row_count, " .
//                            "(sum(m.replystatustyperefid) >  count(1)) as is_unread, " .
//                            "max(created_at) as latest_created, min(fromentityid) as sender, " .
//                            "max(toentityid) as recipient")
//                    ->groupBy("messageid");
//
//        return $q->execute();
//
//    }

    /**
     * Get latest reply for each message
     * @return array    array of key value
     */
    public function getLatestReplyMessages() {
        $conn = $this->getConnection();
        $currentUser = sfContext::getInstance()->getUser()->getAttribute('currentuser');

        $query = "SELECT m.id, m.entityid, m.fromentityid msgfrom, e.firstname, e.lastname, e2.firstname from_firstname, e2.lastname from_lastname,
                    m.subject, left(IFNULL(mr.replybody, m.body), 100) as shortbody, IFNULL(mr.replystatustyperefid, m.messagestatustyperefid) replystatustyperefid,
                    mr.fromentityid replyfrom, IFNULL(mr.toentityid, m.entityid) replyto,
                    m.created_at sent_at, mr.created_at replied_at, T.latest_id FROM messagereply mr
                    inner join (select max(id) as latest_id from messagereply group by messageid) T on mr.id = T.latest_id
                    right join message m on m.id = mr.messageid
                    inner join entity e on e.id = m.entityid
                    inner join entity e2 on e2.id = m.fromentityid
                    where m.entityid = ? or m.fromentityid = ?
                    order by IFNULL(mr.created_at, m.created_at) desc";
        
        $result = $conn->fetchAll($query, array($currentUser->getId(), $currentUser->getId()));

        return $result;
    }
}